package com.informatics.polymer.server.database;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.openscience.cdk.DefaultChemObjectBuilder;
import org.openscience.cdk.exception.CDKException;
import org.openscience.cdk.exception.InvalidSmilesException;
import org.openscience.cdk.interfaces.IAtomContainer;
import org.openscience.cdk.smiles.SmilesParser;
import org.openscience.cdk.smiles.smarts.SMARTSQueryTool;

import com.informatics.polymer.server.propertysearch.RepeatUnitProperty;

/**
 * Class to instantiate a Derby database, create a connection, do substructure
 * search, add/ delete rows in database.
 * 
 * @author ed
 * @version 1.0
 */
public class DatabaseInteraction {
	/**
	 * Sets the descriptor array length.
	 */
	private static final int descriptorSize = 16;
	/**
	 * URL of the database.
	 */
	private final String dbURL = "jdbc:derby:/home/ed/Documents/OpenSource/PolyGWTVersion2/war/RepeatUnitsDB;create=true";
	// "jdbc:derby:"+new
	// String(System.getProperty("user.dir"))+"/war/RepeatUnitsDB;create=true";//
	// "jdbc:derby:RepeatUnitsDB;create=true";
	/**
	 * Name of repeat unit table.
	 */
	private final String tableName = "RepeatUnitsTable";
	/**
	 * Connection name.
	 */
	private Connection conn;
	/**
	 * Statement.
	 */
	private Statement stmt;
	/**
	 * Insert SQL, used in prepared statement.
	 */
	private String insertInfoSQL = "INSERT INTO RepeatUnitsTable VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	/**
	 * Set of results
	 */
	private ResultSetMetaData rsmd;

	/**
	 * Define DatabaseInteraction object.
	 */
	public DatabaseInteraction() {
	}

	/**
	 * Instantiate a database connection.
	 * 
	 * @throws InstantiationException
	 *             - error instantiating the database.
	 * @throws IllegalAccessException
	 *             - error accessing the database.
	 * @throws ClassNotFoundException
	 *             - EmbeddedDriver class not found.
	 * @throws SQLException
	 *             - error with SQL statement.
	 */
	public void init() throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
		conn = DriverManager.getConnection(dbURL);
	}

	/**
	 * Extracts all repeat units in the database.
	 * 
	 * @return ArrayList containing all the repeat unit information in the
	 *         database.
	 */
	public ArrayList<RepeatUnit> getAllRepeatUnits() {
		ArrayList<RepeatUnit> allRepeatUnits = new ArrayList<RepeatUnit>();
		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery("select * from " + tableName);
			ResultSetMetaData rsmd = results.getMetaData();
			int numberCols = rsmd.getColumnCount();
			for (int i = 1; i <= numberCols; i++) {
				// print Column Names
				System.out.print(rsmd.getColumnLabel(i) + "\t\t");
			}
			System.out
					.println("\n-------------------------------------------------");
			while (results.next()) {
				String repeatUnitID = results.getString(1);
				String smileValue = results.getString(2);
				double[] descriptorArray = new double[descriptorSize];
				// Loop over the columns and extract descriptor data.
				for (int i = 0; i < descriptorSize; i++) {
					descriptorArray[i] = results.getDouble(i + 3);
				}
				RepeatUnit repeatUnitDBRowObject = new RepeatUnit(repeatUnitID,
						smileValue, descriptorArray);
				allRepeatUnits.add(repeatUnitDBRowObject);
			}
			results.close();
			stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
		return allRepeatUnits;
	}

	/**
	 * Insert an additional repeat unit into the database.
	 * 
	 * @param repeatUnitID
	 *            - String representation of the repeat unit ID.
	 * @param smilesFragment
	 *            - String representation of the smiles string.
	 * @param descriptorData
	 *            - Array of doubles representing the repeat unit descriptor
	 *            properties.
	 */
	public void insertRepeatUnit(String repeatUnitID, String smilesFragment,
			double[] descriptorData) {
		try {
			stmt = conn.createStatement();
			PreparedStatement pstmt = conn.prepareStatement(insertInfoSQL);
			pstmt.setString(1, repeatUnitID);
			pstmt.setString(2, smilesFragment);
			for (int i = 0; i < descriptorSize; i++) {
				pstmt.setDouble(i + 3, descriptorData[i]);
			}
			// Need to add associated index number.
			pstmt.execute();
			conn.commit();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Create repeat unit table.
	 */
	private void createTable() {
		try {
			stmt = conn.createStatement();
			stmt
					.execute("CREATE TABLE RepeatUnitsTable ( RepeatUnit_ID VARCHAR(30) NOT NULL,SMILE_STRING LONG VARCHAR NOT NULL,ALOGP DOUBLE NOT NULL,"
							+ "ALogP2 DOUBLE NOT NULL,AMR DOUBLE NOT NULL,APol DOUBLE NOT NULL,AromaticAtomCount INTEGER NOT NULL,AromaticBondCount INTEGER NOT NULL,"
							+ "AtomCount INTEGER NOT NULL,BondCount INTEGER NOT NULL,FragmentComplexity DOUBLE NOT NULL,HBACount INTEGER NOT NULL,HBDCount INTEGER NOT NULL,Kappa1 DOUBLE NOT NULL,"
							+ "Kappa2 DOUBLE NOT NULL,Kappa3 DOUBLE NOT NULL,RuleOf5 DOUBLE NOT NULL,TPSA DOUBLE NOT NULL,Index_NO INTEGER NOT NULL"
							+ " CONSTRAINT RepeatUnitsDB_value_PK PRIMARY KEY)");
			stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}

	/**
	 * Shutdown the connection so we do not over use resources.
	 */
	public void shutdown() {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				DriverManager.getConnection(dbURL + ";shutdown=true");
				conn.close();
			}
		} catch (SQLException sqlExcept) {
			throw new RuntimeException(sqlExcept);
		}
	}

	/**
	 * Chemistry Development Kit substructure search.
	 * 
	 * @param querySmile
	 *            - Smiles string posted by user in GUI.
	 * @return A list of repeat units that have the query as a substructure.
	 * @throws CDKException
	 */
	public List<RepeatUnit> getSelectedDBResults(String querySmile)
			throws CDKException {
		List<RepeatUnit> repeatUnitsSelected = new ArrayList<RepeatUnit>();
		try {
			stmt = conn.createStatement();
			ResultSet results = stmt.executeQuery("select * from " + tableName);
			setRsmd(results.getMetaData());
			SmilesParser sp = new SmilesParser(DefaultChemObjectBuilder
					.getInstance());
			IAtomContainer atomContainer;
			// Create new query
			SMARTSQueryTool querytool = new SMARTSQueryTool(querySmile);
			while (results.next()) {
				String repeatUnitID = results.getString(1);
				String smileValue = results.getString(2);
				double[] descriptorArray = new double[descriptorSize];
				for (int i = 0; i < descriptorSize; i++) {
					descriptorArray[i] = results.getDouble(i + 3);
				}
				try {
					atomContainer = sp.parseSmiles(smileValue);
					// Test if database row has the query substructure
					boolean isSubstructure = querytool.matches(atomContainer);
					if (isSubstructure == true) {
						// Convert database rows into repeat unit objects
						RepeatUnit repeatUnitDBRowObject = new RepeatUnit(
								repeatUnitID, smileValue, descriptorArray);
						repeatUnitsSelected.add(repeatUnitDBRowObject);
					}
				} catch (InvalidSmilesException e) {
					e.printStackTrace();
				} catch (CDKException e) {
					e.printStackTrace();
				}
			}
			results.close();
			stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
		return repeatUnitsSelected;
	}

	/**
	 * Method to populate the database in the first instance.
	 * 
	 * @throws IOException
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	public void populateDatabase() throws IOException, InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {
		init();
		createTable();
		String curDir = new String(System.getProperty("user.dir"));
		String fileName = curDir + "/war/RUWithDescriptors.txt";
		BufferedReader br = new BufferedReader(new FileReader(fileName));
		String line;
		int repeatUnitCounter = 0;
		while ((line = br.readLine()) != null) {
			String[] splitLineValues = line.split(",");
			String[] repeatUnit = new String[splitLineValues.length];
			int counter = 0;
			for (String s : splitLineValues) {
				if (s.equals("NaN")) {
					String naNReplaced = s.replace("NaN", "0.0");
					repeatUnit[counter] = naNReplaced;
					counter++;
				} else {
					repeatUnit[counter] = s;
					counter++;
				}
			}
			try {
				stmt = conn.createStatement();
				PreparedStatement pstmt = conn.prepareStatement(insertInfoSQL);
				pstmt.setString(1, repeatUnit[0]);
				pstmt.setString(2, repeatUnit[1]);
				for (int i = 2; i < repeatUnit.length; i++) {
					pstmt.setDouble(i + 1, Double.parseDouble(repeatUnit[i]));
				}
				pstmt.setInt(19, repeatUnitCounter);
				pstmt.execute();
				conn.commit();
				stmt.close();
				repeatUnitCounter++;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}
	}

	/**
	 * Property search method
	 */
	public List<RepeatUnit> propertySearch(
			ArrayList<RepeatUnitProperty> properties) {
		ArrayList<Integer> colSelection = new ArrayList<Integer>();
		List<RepeatUnit> repeatUnitsSelected = new ArrayList<RepeatUnit>();
		for (RepeatUnitProperty r : properties) {
			if (r.getName().equals("ALOGP")) {
				colSelection.add(3);
			}
			if (r.getName().equals("ALogP2")) {
				colSelection.add(4);
			}
			if (r.getName().equals("AMR")) {
				colSelection.add(5);
			}
			if (r.getName().equals("APol")) {
				colSelection.add(6);
			}
			if (r.getName().equals("AromaticAtomCount")) {
				colSelection.add(7);
			}
			if (r.getName().equals("AromaticBondCount")) {
				colSelection.add(8);
			}
			if (r.getName().equals("AtomCount")) {
				colSelection.add(9);
			}
			if (r.getName().equals("BondCount")) {
				colSelection.add(10);
			}
			if (r.getName().equals("FragmentComplexity")) {
				colSelection.add(11);
			}
			if (r.getName().equals("HBACount")) {
				colSelection.add(12);
			}
			if (r.getName().equals("HBDCount")) {
				colSelection.add(13);
			}
			if (r.getName().equals("Kappa1")) {
				colSelection.add(14);
			}
			if (r.getName().equals("Kappa2")) {
				colSelection.add(15);
			}
			if (r.getName().equals("Kappa3")) {
				colSelection.add(16);
			}
			if (r.getName().equals("ruleOf5")) {
				colSelection.add(17);
			}
			if (r.getName().equals("TPSA")) {
				colSelection.add(18);
			}
			// Now can start search
			try {
				stmt = conn.createStatement();
				ResultSet results = stmt.executeQuery("select * from "
						+ tableName);
				rsmd = results.getMetaData();
				while (results.next()) {
					String repeatUnitID = results.getString(1);
					String smileValue = results.getString(2);
					double[] descriptorArray = new double[descriptorSize];
					for (int i = 0; i < descriptorSize; i++) {
						descriptorArray[i] = results.getDouble(i + 3);
					}
					// Loop through property arrayList ->(i-3) because of id and smiles string
					int countConditionsPast = 0;
					for (int i = 0; i < colSelection.size(); i++) {
						if ((descriptorArray[colSelection.get(i)-3] >= properties
								.get(i).getMinimumValue())
								&& (descriptorArray[colSelection.get(i)-3] <= properties
										.get(i).getMaximumValue())) {
							countConditionsPast++;
						}
					}
					// Add repeat unit to output if all conditions met.
					if (countConditionsPast == colSelection.size()) {
						RepeatUnit repeatUnitDBRowObject = new RepeatUnit(
								repeatUnitID, smileValue, descriptorArray);
						repeatUnitsSelected.add(repeatUnitDBRowObject);
					}

				}
			} catch (SQLException sqlExcept) {
				sqlExcept.printStackTrace();
			}
		}
		return repeatUnitsSelected;
	}

	/**
	 * Deletes a row in the database selected by the user.
	 * 
	 * @param rowNumber
	 *            - int value of row number to be deleted.
	 */
	public void deleteEntry(int rowNumber) {
		int counter = 0;
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			ResultSet res = stmt.executeQuery("select * from " + tableName);
			while (res.next()) {
				if (counter == rowNumber) {
					res.deleteRow();
				}
				counter++;
			}
			res.close();
			stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}

	public void removeTable(String tableName) {
		try {
			stmt = conn.createStatement();
			String theDropStatement = "DROP TABLE " + tableName;
			stmt.execute(theDropStatement);
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}

	// Test method
	public static void main(String[] args) throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException, CDKException {
		DatabaseInteraction di = new DatabaseInteraction();
		 di.populateDatabase();
		//di.init();
	//	ArrayList<RepeatUnitProperty> p = new ArrayList<RepeatUnitProperty>();
	//	RepeatUnitProperty r =  new RepeatUnitProperty("AtomCount",30,10);
	//	p.add(r);
	//	List<RepeatUnit> results = di.propertySearch(p);
	//	System.out.println(results.size());
		// List<RepeatUnit> res = di.getSelectedDBResults("C1CCCCC1");
		// System.out.println(res.get(0).getSmilesString());
	}

	/**
	 * @param rsmd the rsmd to set
	 */
	public void setRsmd(ResultSetMetaData rsmd) {
		this.rsmd = rsmd;
	}

	/**
	 * @return the rsmd
	 */
	public ResultSetMetaData getRsmd() {
		return rsmd;
	}
}
